
--Set the database to which these functions will be installed
USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------

--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '<<< Enable SQL Advanced Options - Completed with SUCCESS >>>'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '<<< Enable CLR - Completed with SUCCESS >>>'
END

--Drop the aggregate if it exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_list_agg' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_list_agg]
	PRINT '<<< DROP AGGREGATE [clr].[fn_list_agg] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_median' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_median]
	PRINT '<<< DROP AGGREGATE [clr].[fn_median] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_first' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_first]
	PRINT '<<< DROP AGGREGATE [clr].[fn_first] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_last' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_last]
	PRINT '<<< DROP AGGREGATE [clr].[fn_last] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_nth' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_nth]
	PRINT '<<< DROP AGGREGATE [clr].[fn_nth] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_range' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_range]
	PRINT '<<< DROP AGGREGATE [clr].[fn_range] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_mode' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP AGGREGATE [clr].[fn_mode]
	PRINT '<<< DROP AGGREGATE [clr].[fn_mode] - Completed with SUCCESS >>>'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrAgg') BEGIN 
	DROP ASSEMBLY [ClrAgg]
	PRINT '<<< DROP Assembly ClrAgg - Completed with SUCCESS >>>'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY [ClrAgg] 
--FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrAgg.dll' 
-- Testing only
FROM 'D:\Projects\SqlLib\Project\CLR\Agg\bin\Debug\ClrAgg.dll'
WITH PERMISSION_SET = SAFE

PRINT '<<< Create Assembly ClrAgg - Completed with SUCCESS >>>'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[fn_list_agg]
Type:			Custom aggregate 
Owner:					
Description:	Returns a delimited list of values based on records in the GROUP BY  
Parameters:		@value = value (field name) to use for creating the list
				@delimiter = character(s) to use as delimiter
				@qualifier = character(s) to use as test qualifier
Dependens on:  	ClrAgg.dll
Usage:			SELECT City , clr.fn_list_agg(Name,',','') FROM Customer GROUP BY City
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_list_agg&referringTitle=Documentation		
Version:		20121017
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/17/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE [clr].[fn_list_agg] 
	( @value NVARCHAR(MAX)
	, @delimiter NVARCHAR(255)
	, @qualifier NVARCHAR(255))
RETURNS NVARCHAR(MAX)	
EXTERNAL NAME ClrAgg.ListAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_list_agg' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_list_agg] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_list_agg] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_median]
Type:			Custom aggregate 
Owner:					
Description:	Returns a delimited list of values based on records in the GROUP BY  
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_median(Amount,',','') FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_median&referringTitle=Documentation		
Version:		20121017
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/17/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_median
(@input float)
RETURNS float
EXTERNAL NAME ClrAgg.MedianAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_median' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_median] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_median] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_first]
Type:			Custom aggregate 
Owner:					
Description:	Returns first row in a group by   
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_first(Amount) FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_first&referringTitle=Documentation		
Version:		20121023
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/23/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_first
(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ClrAgg.FirstAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_first' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_first] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_first] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_last]
Type:			Custom aggregate 
Owner:					
Description:	Returns first row in a group by   
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_last(Amount) FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_last&referringTitle=Documentation		
Version:		20121023
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/23/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_last
(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ClrAgg.LastAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_last' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_last] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_last] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_nth]
Type:			Custom aggregate 
Owner:					
Description:	Returns first row in a group by   
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_nth(Amount,5) FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_nth&referringTitle=Documentation		
Version:		20121023
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/23/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_nth
(@value NVARCHAR(MAX)
,@rec_no BIGINT)
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ClrAgg.NthAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_nth' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_nth] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_nth] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


/*================================================================================================
Name:			[clr].[fn_range]
Type:			Custom aggregate 
Owner:					
Description:	Returns first row in a group by   
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_range(Amount) FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_range&referringTitle=Documentation		
Version:		20121023
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/24/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_range
(@value FLOAT)
RETURNS FLOAT
EXTERNAL NAME ClrAgg.RangeAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_range' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_range] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_range] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


/*================================================================================================
Name:			[clr].[fn_mode]
Type:			Custom aggregate 
Owner:					
Description:	Returns the most common value in a group by   
Parameters:		@input = value (field name) to use for creating the list
Dependens on:  	ClrAgg.dll
Usage:			SELECT Company , clr.fn_mode(category) FROM Invoice GROUP BY Company
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_mode&referringTitle=Documentation		
Version:		20121024
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/24/2012 - Adrian E Dudau - Aggregate created
================================================================================================*/
CREATE AGGREGATE clr.fn_mode
(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME ClrAgg.ModeAgg

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_mode' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE AGGREGATE [clr].[fn_mode] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE AGGREGATE [clr].[fn_mode] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

